package com.gxa.shop.dao.order.impl;

import com.alibaba.fastjson.JSON;
import com.gxa.shop.bean.dto.OrderDto;
import com.gxa.shop.bean.pojo.*;
import com.gxa.shop.bean.vo.OrderMyVo;
import com.gxa.shop.bean.vo.OrderVo;
import com.gxa.shop.commons.jdbc.JDBCUtil;
import com.gxa.shop.dao.order.OrderDao;


import java.math.BigInteger;
import java.time.LocalDateTime;
import java.util.*;

/**
 * com.gxa.shop.dao.impl
 * User: coma
 * Description:
 * Version: V1.0
 */
public class OrderDaoImpl implements OrderDao {


    @Override
    public List<Address> findUserAddresses(Integer userId) {
        String sql = "SELECT * FROM address WHERE user_id = ?";

        JDBCUtil jdbcUtil = new JDBCUtil();
        List<Map<String, Object>> query = jdbcUtil.query(sql, userId);
        if (query.size() > 0) {
            List<Address> list = new ArrayList<>();
            for (Map<String, Object> row : query) {
                Address address = new Address();
                address.setAddressId((Integer) row.get("address_id"));
                address.setUserId((Integer) row.get("user_id"));
                address.setRecipientName((String) row.get("recipient_name"));
                address.setRecipientPhone((String) row.get("recipient_phone"));
                address.setExpressfeeId((Integer) row.get("expressfee_id"));
                address.setRecipientAddress((String) row.get("recipient_address"));
                list.add(address);
            }
            return list;
        }
        return null;

    }


    @Override
    public List<Product> findUserCartProducts(Integer userId) {
        String sql = "SELECT p.* FROM product p JOIN cart c ON p.product_id = c.product_id WHERE c.user_id = ?";
        JDBCUtil jdbcUtil = new JDBCUtil();
        List<Map<String, Object>> query = jdbcUtil.query(sql, userId);

        if (query.size() > 0) {
            List<Product> list = new ArrayList<>();
            for (Map<String, Object> row : query) {
                Product product = new Product();
                product.setProductId((Integer) row.get("product_id"));
                product.setProductName((String) row.get("product_name"));
                product.setUnitPrice((Double) row.get("unit_price"));
                product.setProductImage((String) row.get("product_image"));
                product.setProductColor((String) row.get("product_color"));
                product.setProductSize((String) row.get("product_size"));
                product.setProductMaterial((String) row.get("product_material"));
                product.setWeight((Double) row.get("weight"));
                product.setStock((Integer) row.get("stock"));
                product.setSales((Integer) row.get("sales"));

                // 根据数据库字段类型选择合适的方法
                Object launchTimeObj = row.get("launch_time");
                if (launchTimeObj instanceof java.sql.Date) {
                    // 如果数据库字段是 DATE 类型
                    product.setLaunchTime(new Date(((java.sql.Date) launchTimeObj).getTime()));
                } else if (launchTimeObj instanceof java.sql.Timestamp) {
                    // 如果数据库字段是 TIMESTAMP 类型
                    product.setLaunchTime(new Date(((java.sql.Timestamp) launchTimeObj).getTime()));
                }

                product.setDiscountId((Integer) row.get("discount_id"));
                list.add(product);
            }
            return list;
        }
        return null;
    }

    @Override
    public Regin findRegionByExpressfeeId(Integer expressfeeId) {
        String sql = "SELECT * FROM regin WHERE expressfee_id = ?";
        JDBCUtil jdbcUtil = new JDBCUtil();
        List<Map<String, Object>> query = jdbcUtil.query(sql, expressfeeId);
        if (query.size() > 0) {
            List<Regin> list = new ArrayList<>();
            for (Map<String, Object> row : query) {
                Regin regin = new Regin();
                regin.setExpressfeeId((Integer) row.get("expressfee_id"));
                regin.setRegin((String) row.get("regin"));
                regin.setReginMoney((Double) row.get("regin_money"));
                return regin;
            }

        }
        return null;
    }

    @Override
    public Address getAddressById(Integer addressId) {
        String sql = "SELECT * FROM address WHERE address_id = ?";
        JDBCUtil jdbcUtil = new JDBCUtil();
        List<Map<String, Object>> query = jdbcUtil.query(sql, addressId);

        if (!query.isEmpty()) {
            Map<String, Object> row = query.get(0);
            Address address = new Address();
            address.setAddressId((Integer) row.get("address_id"));
            address.setUserId((Integer) row.get("user_id"));
            address.setRecipientName((String) row.get("recipient_name"));
            address.setRecipientPhone((String) row.get("recipient_phone"));
            address.setExpressfeeId((Integer) row.get("expressfee_id"));
            address.setRecipientAddress((String) row.get("recipient_address"));
            return address;
        }
        return null;
    }

    @Override
    public Integer saveOrder(Orders orders) {
        String sql = "INSERT INTO orders (user_id, total_price, address_id, payment_method, order_status, order_time) VALUES (?, ?, ?, ?, ?, NOW())";
        JDBCUtil jdbcUtil = new JDBCUtil();
        // 执行插入并尝试获取最后一个插入的ID
        int resultRows = jdbcUtil.update(sql, orders.getUserId(), orders.getTotalPrice(), orders.getAddressId(), orders.getPaymentMethod(), orders.getOrderStatus());

        // 获取最后一个插入的ID（假设是MySQL数据库）
        List<Map<String, Object>> lastInsertIdResult = jdbcUtil.query("SELECT LAST_INSERT_ID() as id");
        if (!lastInsertIdResult.isEmpty()) {
            Object idObject = lastInsertIdResult.get(0).get("id");

            if (idObject instanceof BigInteger) {
                return ((BigInteger) idObject).intValue();
            } else if (idObject instanceof Long) {
                return ((Long) idObject).intValue();
            } else if (idObject instanceof Integer) {
                return (Integer) idObject;
            }
        }

        return -1; // 如果无法获得ID，则返回-1表示失败
    }


    @Override
    public boolean saveOrderItems(Integer orderId, List<OrderItems> orderItems) {
        String sql = "INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)";
        JDBCUtil jdbcUtil = new JDBCUtil();
        for (OrderItems item : orderItems) {
            // 设置每个 OrderItems 的 orderId
            item.setOrderId(orderId);
            jdbcUtil.update(sql, orderId, item.getProductId(), item.getQuantity());
        }
        return true;
    }

    @Override
    public List<Orders> findByOrderId(Integer orderId) {
        String sql = "SELECT * FROM orders WHERE order_id=?;";
        JDBCUtil jdbcUtil = new JDBCUtil();
        List<Map<String, Object>> query = jdbcUtil.query(sql, orderId);
        if (!query.isEmpty()) {
            List<Orders> list = new ArrayList<>();
            for (Map<String, Object> row : query) {
                Orders orders = new Orders();
                orders.setOrderId((Integer) row.get("order_id"));
                orders.setOrderStatus((String) row.get("order_status"));
                Object orderTimeObj = row.get("order_time");
                Object shipTimeObj = row.get("ship_time");
                if (orderTimeObj instanceof java.sql.Timestamp) {
                    orders.setOrderTime((java.sql.Timestamp) orderTimeObj);
                }
                if (shipTimeObj instanceof java.sql.Timestamp) {
                    orders.setShipTime((java.sql.Timestamp) shipTimeObj);
                }
                orders.setPaymentMethod((String) row.get("payment_method"));
                ;
                orders.setTotalPrice((double) row.get("total_price"));
                orders.setAddressId((Integer) row.get("address_id"));
                orders.setUserId((Integer) row.get("user_id"));
                list.add(orders);
            }
            return list;
        }
        return null;
    }

    @Override
    public int updateOrder(OrderDto orderDto) {
        String sql = "update orders set ship_time=now(),order_status=? ,payment_method=? where order_id=?";
        JDBCUtil jdbcUtil = new JDBCUtil();
        return jdbcUtil.update(sql, orderDto.getOrderStatus(), orderDto.getPaymentMethod(), orderDto.getOrderId());
    }

    @Override
    public List<OrderVo> selectOrder(OrderDto orderDto) {
        StringBuffer sql = new StringBuffer("SELECT " +
                " o.order_id, " +
                " a.username, " +
                " a.phone, " +
                " p.product_name, " +
                " p.unit_price, " +
                " oi.quantity, " +
                " ad.recipient_name, " +
                " ad.recipient_phone, " +
                " o.order_time, " +
                " o.ship_time, " +
                " o.order_status, " +
                " o.payment_method, " +
                " o.total_price " +
                " FROM " +
                " account a " +
                " JOIN " +
                " orders o ON a.user_id = o.user_id " +
                " JOIN " +
                " order_items oi ON o.order_id = oi.order_id " +
                " JOIN " +
                " product p ON oi.product_id = p.product_id " +
                " JOIN " +
                " address ad ON o.address_id = ad.address_id " +
                " WHERE 1=1 ");

        List<Object> params = new ArrayList<>();

        if (orderDto.getUserName() != null) {
            sql.append(" AND a.username LIKE CONCAT('%', ?, '%')" );
            params.add(orderDto.getUserName());
        }

        if (orderDto.getUserPhone() != null) {
            sql.append(" AND a.phone LIKE CONCAT('%', ?, '%')" );
            params.add(orderDto.getUserPhone());
        }

        if (orderDto.getProductName() != null) {
            sql.append(" AND p.product_name LIKE CONCAT('%', ?, '%')" );
            params.add(orderDto.getProductName());
        }

        if (orderDto.getRecipientName() != null) {
            sql.append(" AND ad.recipient_name LIKE CONCAT('%', ?, '%')" );
            params.add(orderDto.getRecipientName());
        }

        sql.append(" order  by o.order_id  desc limit " + (orderDto.getPage() - 1) * orderDto.getLimit() + "," + orderDto.getLimit());
        JDBCUtil jdbcUtil = new JDBCUtil();
        List<Map<String, Object>> mapList = jdbcUtil.query(sql.toString(), params.toArray());
        List<OrderVo> listOrderVo = new ArrayList<>();
        for (Map<String, Object> map : mapList
        ) {
            OrderVo orderVo = new OrderVo();
            orderVo.setOrderId((Integer) map.get("order_id"));
            orderVo.setUserName((String) map.get("username"));
            orderVo.setUserPhone((String) map.get("phone"));
            orderVo.setProductName((String) map.get("product_name"));
            orderVo.setUnitPrice((Double) map.get("unit_price"));
            orderVo.setQuantity((Integer) map.get("quantity"));
            orderVo.setRecipientName((String) map.get("recipient_name"));
            orderVo.setRecipientPhone((String) map.get("recipient_phone"));
            orderVo.setOrderTime((LocalDateTime) map.get("order_time"));
            orderVo.setShipTime((LocalDateTime) map.get(("ship_time")));
            orderVo.setOrderStatus((String) map.get("order_status"));
            orderVo.setPaymentMethod((String) map.get("payment_method"));
            orderVo.setTotalPrice((Double) map.get("total_price"));
            listOrderVo.add(orderVo);
        }
        return listOrderVo;
    }

    @Override
    public Long getAllOrderTotal(OrderDto orderDto) {
        StringBuffer sql = new StringBuffer("SELECT  " +
                "    count(*) as total  " +
                "FROM  " +
                "    account a " +
                "JOIN  " +
                "    orders o ON a.user_id = o.user_id " +
                "JOIN  " +
                "    order_items oi ON o.order_id = oi.order_id " +
                "JOIN  " +
                "    product p ON oi.product_id = p.product_id " +
                "JOIN  " +
                "    address ad ON o.address_id = ad.address_id WHERE 1=1 ");
        List<Object> list = new ArrayList<>();

        if (orderDto.getUserName() != null) {
            sql.append(" and a.username like concat ('%',?,'%') ");
            list.add(orderDto.getUserName());
        }
        if (orderDto.getUserPhone() != null) {
            sql.append(" and a.phone like concat ('%',?,'%') ");
            list.add(orderDto.getUserPhone());
        }
        if (orderDto.getProductName() != null) {
            sql.append(" and  p.product_name like concat ('%',?,'%') ");
            list.add(orderDto.getProductName());
        }
        if (orderDto.getRecipientName() != null) {
            sql.append(" and ad.recipient_name like concat ('%',?,'%') ");
            list.add(orderDto.getRecipientName());
        }
        if (orderDto.getRecipientPhone() != null) {
            sql.append(" and ad.recipient_phone like concat ('%',?,'%')");
            list.add(orderDto.getRecipientPhone());
        }
//        sql.append(" order  by o.order_id  desc limit " + (orderDto.getPage() - 1) * orderDto.getLimit() + "," + orderDto.getLimit());
        JDBCUtil jdbcUtil = new JDBCUtil();
        List<Map<String, Object>> mapList = jdbcUtil.query(sql.toString(), list.toArray());
        Long ct = 0L;
        if (mapList.size() > 0) {
            //该变
            ct = (Long) mapList.get(0).get("total");
        }
        return ct;
    }

    @Override
    public int getTotalCount(Integer userId, Integer status) {
        String sql = "SELECT COUNT(*) As total FROM orders WHERE user_id=?"; // 默认查询全部订单数量
        List<Object> params = new ArrayList<>();
        params.add(userId);

        // 根据状态调整 SQL 和参数
        if (status != null && status >= 0 && status < 4) {
            if (status > 0) { // 只有当 status 不为 0 时才添加 order_status 条件
                sql += " AND order_status = ?"; // 确保这里有一个空格
                String statusW = getOrderStatus(status);
                if (statusW == null) {
                    throw new IllegalArgumentException("Invalid status code: " + status);
                }
                params.add(statusW);
            }
        } else if (status != null) {
            throw new IllegalArgumentException("Invalid status code: " + status);
        }

        JDBCUtil jdbcUtil = new JDBCUtil();
        List<Map<String, Object>> list = jdbcUtil.query(sql, params.toArray());

        if (list != null && !list.isEmpty()) {
            // 获取第一个结果集中的 count(*) 的值
            Map<String, Object> result = list.get(0);
            return ((Number) result.get("total")).intValue();
        }

        return 0; // 没有找到任何记录时返回 0
    }

    private String getOrderStatus(Integer status) {
        switch (status) {
            case 1:
                return "待发货";

            case 2:
                return "已发货";

            case 3:
                return "已收货";

            default:
                return null;
        }
    }

    @Override
    public List<OrderMyVo> getOrderInfo(Integer userId, Integer status,Integer page, Integer pageSize) {
        // 验证分页参数
        if (pageSize == null || pageSize <= 0) {
            throw new IllegalArgumentException("Pagesize must be a positive integer.");
        }
        if (page == null || page+1 < 1) {
            throw new IllegalArgumentException("Page number must be a positive integer starting from 1.");
        }

        String sql = "SELECT " +
                "o.order_id, " +
                "o.order_time, " +
                "o.payment_method, " +
                "o.order_status, " +
                "a.recipient_address, " +
                "o.total_price, " +
                "JSON_ARRAYAGG(" +
                "JSON_OBJECT(" +
                "'productName', p.product_name, " +
                "'productImage', p.product_image, " +
                "'productColor', p.product_color, " +
                "'productSize', p.product_size, " +
                "'unitPrice', p.unit_price, " +
                "'quantity', oi.quantity" +
                ")) AS products " +
                "FROM " +
                "orders o " +
                "LEFT JOIN " +
                "address a ON o.address_id = a.address_id " +
                "JOIN " +
                "order_items oi ON o.order_id = oi.order_id " +
                "JOIN " +
                "product p ON oi.product_id = p.product_id " +
                "WHERE " +
                "o.user_id = ?";

        List<Object> params = new ArrayList<>();
        params.add(userId);

        // 根据状态调整 SQL 和参数
        if (status != null && status >= 0 && status < 4) {
            if (status > 0) {
                sql += " AND order_status = ?";
                String statusW = getOrderStatus(status);
                if (statusW == null) {
                    throw new IllegalArgumentException("Invalid status code: " + status);
                }
                params.add(statusW);
            }
        } else if (status != null) {
            throw new IllegalArgumentException("Invalid status code: " + status);
        }

        sql += " GROUP BY o.order_id LIMIT ? OFFSET ? ";
        params.add(pageSize);
        params.add((page-1)  * pageSize);

        System.out.println(sql);
        JDBCUtil jdbcUtil = new JDBCUtil();
        List<Map<String, Object>> list = jdbcUtil.query(sql, params.toArray());

        List<OrderMyVo> listOrderVo = new ArrayList<>();
        for (Map<String, Object> map : list) {
            OrderMyVo orderMyVo = new OrderMyVo();
            orderMyVo.setOrderId((Integer) map.get("order_id"));
            orderMyVo.setOrderTime((LocalDateTime) map.get("order_time"));
            orderMyVo.setPaymentMethod((String) map.get("payment_method"));
            orderMyVo.setOrderStatus((String) map.get("order_status"));
            orderMyVo.setRecipientAddress((String) map.get("recipient_address"));
            orderMyVo.setTotalPrice((Double) map.get("total_price"));

            String productsJson = (String) map.get("products");
            if (productsJson != null && !productsJson.isEmpty()) {
                List<Product> productList = JSON.parseArray(productsJson, Product.class);
                orderMyVo.setProducts(productList);
            } else {
                orderMyVo.setProducts(Collections.emptyList());
            }

            listOrderVo.add(orderMyVo);
        }
        return listOrderVo;
    }

}




